package com.dao;

import java.sql.*;
import java.util.ArrayList;

import com.Controller.newsSerclet;
import com.moddel.ImageB;
import com.moddel.makeB;

public class imageDao {
	private final static String DRIVER = "com.mysql.jdbc.Driver";
	private final static String URL = "jdbc:mysql://localhost:3306/mafx";
	private final static String USERNAME = "root";
	private final static String PASSWORD = "root";
	private int pagesize=0;
	private int pagenow=1;
	public ArrayList<ImageB> selectImage(){
		ArrayList<ImageB> list=new ArrayList<ImageB>();
		Connection ct=null;
		PreparedStatement ps=null;
		ResultSet rSet=null;
		String sql="select id,img from image";
		try{
			Class.forName(DRIVER);
			ct=DriverManager .getConnection(URL,USERNAME,PASSWORD);
			ps=ct.prepareStatement(sql);
			rSet=ps.executeQuery();
			while(rSet.next()){
				ImageB image=new ImageB();
				image.setID(rSet.getInt(1));
				image.setAddress(rSet.getString(2));
				list.add(image);
				
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			
				try {
					if(rSet!=null)
					rSet.close();
					if(ps!=null)
						ps.close();
					if(ct!=null)
						ct.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			
		}
		return list;
	}
	public ArrayList<ImageB> selectPhotoNews(int id){
		ArrayList<ImageB> list=new ArrayList<ImageB>();
		Connection ct=null;
		PreparedStatement ps=null;
		ResultSet rSet=null;
		String sql="select address from photonews where id="+id+"";
		try{
			Class.forName(DRIVER);
			ct=DriverManager .getConnection(URL,USERNAME,PASSWORD);
			ps=ct.prepareStatement(sql);
			rSet=ps.executeQuery();
			while(rSet.next()){
				ImageB pn=new ImageB();
				pn.setID(id+51);
				pn.setAddress(rSet.getString(1));
				list.add(pn);
				
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			
				try {
					if(rSet!=null)
					rSet.close();
					if(ps!=null)
						ps.close();
					if(ct!=null)
						ct.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			
		}
		return list;
	}
	public ArrayList<makeB> selectAllMake(){
		ArrayList<makeB> list=new ArrayList<makeB>();
		Connection ct=null;
		PreparedStatement ps1=null;
		ResultSet rSet1=null;
		PreparedStatement ps2=null;
		ResultSet rSet2=null;
		String sql="select id,name,makeDetail from bluemsunMake";
		try{
			Class.forName(DRIVER);
			ct=DriverManager .getConnection(URL,USERNAME,PASSWORD);
			ps1=ct.prepareStatement(sql);
			rSet1=ps1.executeQuery();
			while(rSet1.next()){
				makeB mB=new makeB();
				mB.setId(rSet1.getInt(1));
				mB.setName(rSet1.getString(2));
				mB.setMakeDetail(rSet1.getString(2));
			    ps2=ct.prepareStatement("select makeImage.image from bluemsunMake,makeImage where bluemsunMake.id="+rSet1.getInt(1)+" and (makeImage.id=bluemsunMake.iid);");
			    rSet2=ps2.executeQuery();
			    rSet2.next();
			    mB.setMakeImage(rSet2.getString(1));
			    list.add(mB);
			}
			
		}catch(Exception ex){
			ex.printStackTrace();
		}
	    return list;
	}
	public ArrayList<makeB> selectAllMake(int pageSize,int pageNow){
		ArrayList<makeB> list=new ArrayList<makeB>();
		Connection ct=null;
		PreparedStatement ps1=null;
		ResultSet rSet1=null;
		PreparedStatement ps2=null;
		ResultSet rSet2=null;
		String sql="select id,name from bluemsunMake LIMIT ?,?";
		try{
			Class.forName(DRIVER);
			ct=DriverManager .getConnection(URL,USERNAME,PASSWORD);
			ps1=ct.prepareStatement(sql);
			ps1.setInt(1, (pageNow-1)*pageSize);
			 ps1.setInt(2, pageSize);
			rSet1=ps1.executeQuery();
			while(rSet1.next()){
				makeB mB=new makeB();
				mB.setId(rSet1.getInt(1));
				mB.setName(rSet1.getString(2));
			    ps2=ct.prepareStatement("select makeImage.image from bluemsunMake,makeImage where bluemsunMake.id="
				+rSet1.getInt(1)+" and (makeImage.id=bluemsunMake.iid)");
			    rSet2=ps2.executeQuery();
			    rSet2.next();
			    mB.setMakeImage(rSet2.getString(1));
			    list.add(mB);
			    System.out.println("efarew==="+list);
			}
			
		}catch(Exception ex){
			ex.printStackTrace();
		}
	    return list;
	}
	public boolean insertMake(int id,int iid,String makename,String makeImage){
		Connection ct=null;
		PreparedStatement ps1=null;
		PreparedStatement ps2=null;
		int resultInt=0;
		String sql1="insert into bluemsunMake(id,name,iid) values("+id+",'"+makename+"',"+iid+")"; 
		String sql2="insert into makeImage values("+iid+",'"+makeImage+"')";
		try{
			Class.forName(DRIVER);
			ct=DriverManager .getConnection(URL,USERNAME,PASSWORD);
			ps1=ct.prepareStatement(sql1);
			resultInt=ps1.executeUpdate();
			ps2=ct.prepareStatement(sql2);
			
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return true;
	}
	/*public static void main(String[] args){
		imageDao sm=new imageDao();
		ArrayList<makeB> list_2=new ArrayList<makeB>();
		list_2=sm.selectAllMake(4,1);
		System.out.println("dvfdsg==="+list_2);
	}*/
}
